<?php
 /**
  * Msg模型类(数据库模型)
  *   
  * @author wenqiang@6rooms.com
  * @version $Id: Message.class.php,v 1.0 2013/11/27 17:19:01 gaowenqiang Exp $   
  * @package sr.model
  *
  */
class Sr_Model_Message extends Sr_Model_Base
{
	const STAT_TYPE_DAY = 1;// 按天统计
	const STAT_TYPE_HOUR = 2;// 按小时统计
	const STAT_TYPE_HALFHOUR = 3;// 按半小时统计

	/**
	 * 表名
	 * @var string
	 *
	 */
	protected $name = 'msg';

	/**
	 * 主键名
	 * @var string
	 *
	 */
	protected $primary = 'id';
	
	/**
	 * 构造器
	 * @var array
	 *
	 */
	public function __construct($args = null)
	{
		parent::__construct($args);

		$this->initExpr();
	}
	
	/**
	 * 初始化表达式数据
	 *
	 */
	protected function initExpr()
	{
		if (empty($this->expr)) {
			$format = array(
				'date' => "FROM_UNIXTIME(%s, '%%Y-%%m-%%d')",
				'hour' => "FROM_UNIXTIME(%s, '%%H')",
				'minute' => "FROM_UNIXTIME(%s, '%%m')",
				'hm' => "FROM_UNIXTIME(%s, '%%H-%%m')",
			);
			$tmr = array(
				'date' => sprintf($format['date'], 'tmr'),
				'hour' => sprintf($format['hour'], 'tmr'),
				'minute' => sprintf($format['minute'], 'tmr'),
				'hm' => sprintf($format['hm'], 'tmr')
			);

			$this->expr = array(
				// day
				'STAT_COL_DATE' => $tmr['date'],
				// hour
				'STAT_COL_HOUR' => $tmr['hour'],
				// halfhour
				'STAT_COL_HALFHOUR' => "IF({$tmr['minute']} >= 30, CONCAT('{$tmr['hour']}', '-30', CONCAT('{$tmr['hour']}', '-00')",
				// 统计日期(源数据)
				'STAT_DATE' => $tmr['date'],
				// 全部发送数量
				'COUNT_SEND_ALL' => "COUNT(IF(tms <> 0, 1, NULL))", 
				// 发送成功数量
				'COUNT_SUCCESS' => "SUM(IF(tms > 0, 1, 0))",
				// 发送失败数量
				'COUNT_FAIL' => "SUM(IF(tms < 0, 1, 0))",
				// 未处理数量
				'COUNT_PENDING' => "SUM(IF(tms = 0, 1, 0))",
				// 平均发送时间(成功)
				'AVG_SPEND_TIME_SUCCESS' => "IFNULL(
										SUM(IF (tms > 0, tms - tmr, 0)) 
										/ COUNT(IF (tms > 0, tms - tmr, NULL)),
										NULL
									)",
				// 平均发送时间(失败)
				'AVG_SPEND_TIME_FAIL' => "IFNULL(
										SUM(IF (tms < 0, ABS(tms) - tmr, 0)) 
										/ COUNT(IF (tms < 0, ABS(tms) - tmr, NULL)),
										NULL
									)",
				// 平均发送时间(成功+失败)
				'AVG_SPEND_TIME' => "IFNULL(
										SUM(IF (ABS(tms) > 0, ABS(tms) - tmr, 0)) 
										/ COUNT(IF (ABS(tms) > 0, ABS(tms) - tmr, NULL)),
										NULL
									)",
			);
		}
	}

	/*
	 * 生成查询条件
	 * @param array $cond 查询条件
	 *	start int 超始时间戳
	 *  end int 结束时间戳
	 *  prod int 产品id
	 *  status int 1.值 > 0 发送成功/ 2. 值 < 0 发送失败 / 3. 值 = 0 未处理
	 *  token string 设备标识Token值
	 *  mid string 应用自定义ID值
	 * @return string
	 *
	 */
	protected function where($cond) 
	{
		$where = array();
		if (isset($cond['start']) && isset($cond['end']) && strlen($cond['start']) && strlen($cond['end'])) {
			$where[] = "tmr >= {$cond['start']} AND tmr <= {$cond['end']}";		
		}
		
		if (isset($cond['prod']) && !empty($cond['prod'])) {
			$where[] = "prod = {$cond['prod']}";
		}

		if (isset($cond['status'])) {
			$operators = (int)$cond['status'] === 0 ? '=' : ((int)$cond['status'] > 0 ? '>' : '<');
			$where[] = "tms {$operators} 0";
		}
		
		if (isset($cond['token']) && strlen($cond['token'])) {
			$where[] = "token = '{$cond['token']}'";
		}

		if (isset($cond['mid']) && strlen($cond['mid'])) {
			$where[] = "mid = '{$cond['mid']}'";
		}
		
		if (isset($cond['hoststr']) && strlen($cond['hoststr'])) {
			$where[] = "hoststr = '{$cond['hoststr']}'";
		}

		return count($where) ? implode(' AND ', $where) : '';
	}

	/*
	 * 获取记录总数
	 * @param array $cond 查询条件
	 *	start int 超始时间戳
	 *  end int 结束时间戳
	 *  prod int 产品id
	 *  statType 1/按小时统计 2/按天统计
	 *  status 1.值 > 0 发送成功/ 2. 值 < 0 发送失败 / 3. 值 = 0 未处理
	 *  token string 设备标识Token值
	 *  mid string 应用自定义ID值
	 * @return int
	 *
	 */
	public function getCount($cond)
	{
		$field = 'COUNT(*)';
		$where = $this->where($cond);

		return (int)$this->fetchOne($where, $field);
	}

	/**
	 * push统计(实时)
	 * @param array $cond 查询条件
	 *	start int 超始时间戳
	 *  end int 结束时间戳
	 *  prod int 产品id
	 *  statType 1/按小时统计 2/按天统计
	 * @return array
	 *
	 */
	public function stat($cond)
	{
		// 列名
		$columnExprs = array(
			'STAT_DATE', 
			'COUNT_SUCCESS', 
			'AVG_SPEND_TIME_SUCCESS',
			'COUNT_FAIL',
			'AVG_SPEND_TIME_FAIL',
			'COUNT_PENDING',
		);

		// 添加统计列
		switch ((int)$cond['statType']) {
			case self::STAT_TYPE_DAY :
				$columnExprs['STAT_COL_DATE'] = 'stat_col';
				break;
			case self::STAT_TYPE_HOUR :
				$columnExprs['STAT_COL_HOUR'] = 'stat_col';
				break;
			case self::STAT_TYPE_HALFHOUR :
				$columnExprs['STAT_COL_HALFHOUR'] = 'stat_col';
				break;
			default:
				return false;
		}
		// 生成列
		$column = $this->column($columnExprs);
		$from = $this->name;
		$where = 'WHERE ' . $this->where($cond);
		$group = "GROUP BY stat_col";
		$order = "ORDER BY stat_col DESC";
		$sql = $this->sql($column, $from, $where, $group, $order);

		return $this->fetchAll($sql);
	}
	
	/**
	 * Push统计(汇总-实时)
	 * @param array $cond 查询条件
	 *	start int 超始时间戳
	 *  end int 结束时间戳
	 *  prod int 产品id
	 * @return array
	 *
	 */
	public function statTotal($cond)
	{
		$column = $this->column(array(
			'COUNT_SEND_ALL',
			'COUNT_SUCCESS', 
			'AVG_SPEND_TIME_SUCCESS',
			'COUNT_FAIL', 
			'AVG_SPEND_TIME_FAIL',
			'COUNT_PENDING'));
		$from = $this->name;
		$where = 'WHERE ' . $this->where($cond);
		$sql = $this->sql($column, $from, $where);

		return $this->fetchRow($sql);
	}
	
	/**
	 * 按主机进行分组统计
	 *
	 */
	public function getStatByHost($cond)
	{
		$hostColumn = 'hoststr';
		// 列名key
		$columnExprs = array(
			'COUNT_SEND_ALL',
			'COUNT_SUCCESS',
			'AVG_SPEND_TIME_SUCCESS',
			'COUNT_FAIL',
			'AVG_SPEND_TIME_FAIL',
			$hostColumn,
		);
		$column = $this->column($columnExprs);
		$from = $this->name;
		$where = 'WHERE tms <> 0 AND ' . $this->where($cond);
		// 生成分组,排序语句
		$group = "GROUP BY {$hostColumn}";
		$order = "ORDER BY {$hostColumn}";
		$sql = $this->sql($column, $from, $where, $group, $order);

		return $this->fetchAll($sql);
	}
	
	/**
	 * push统计(按主机, 实时)
	 * @param array $cond 查询条件
	 *	start int 超始时间戳
	 *  end int 结束时间戳
	 *  prod int 产品id
	 *  host string 主机
	 *  statType 1/按小时统计 2/按天统计
	 * @return array
	 *
	 */
	public function getStatDetailByHost($cond)
	{
		// 列名
		$columnExprs = array(
			'STAT_DATE',
			'COUNT_SEND_ALL',
			'COUNT_SUCCESS',
			'AVG_SPEND_TIME_SUCCESS',
			'COUNT_FAIL',
			'AVG_SPEND_TIME_FAIL',
		);
		// 添加统计列
		switch ((int)$cond['statType']) {
			case self::STAT_TYPE_DAY :
				$datetimeExpr = 'STAT_COL_DATE';
				break;
			case self::STAT_TYPE_HOUR :
				$datetimeExpr = 'STAT_COL_HOUR';
				break;
			case self::STAT_TYPE_HALFHOUR :
				$datetimeExpr = 'STAT_COL_HALFHOUR';
				break;
			default:
				return false;
		}
		// 主机列名
		$hostColumn = 'hoststr';
		$columnExprs[] = $hostColumn;
		$columnExprs[$datetimeExpr] = 'stat_col';
		$column = $this->column($columnExprs);
		
		$from = $this->name;
		// 生成条件语句
		$where = 'WHERE tms <> 0 AND ' . $this->where($cond);
		// 生成分组,排序语句
		$group = "GROUP BY ";
		$order = "ORDER BY ";
		if ($cond['showType'] === 'date') {
			$group .= "stat_col, {$hostColumn}";
			$order .= "stat_col DESC, {$hostColumn}";
		} else {
			$group .= "{$hostColumn}, stat_col";
			$order .= "{$hostColumn}, stat_col DESC";
		}
		$sql = $this->sql($column, $from, $where, $group, $order);

		return $this->fetchAll($sql);
	}
	
	/**
	 * push统计详细记录查询
	 * @param array $cond 查询条件
	 *	start int 超始时间戳
	 *  end int 结束时间戳
	 *  prod int 产品id
	 *  statType 1/按小时统计 2/按天统计
	 *  status 1.值 > 0 发送成功/ 2. 值 < 0 发送失败 / 3. 值 = 0 未处理
	 * @param array $page 分页参数
	 *  size int 每次获取数量
	 *  offset int 起始下标
	 * @return array
	 *
	 */
	public function getList($cond, $page = array())
	{
		$columnExprs = array('*');
		// 添加统计列
		switch ((int)$cond['statType']) {
			case self::STAT_TYPE_DAY :
				$columnExprs['STAT_COL_DATE'] = 'datetime';
				break;
			case self::STAT_TYPE_HOUR :
				$columnExprs['STAT_COL_HOUR'] = 'datetime';
				break;
			case self::STAT_TYPE_HALFHOUR :
				$columnExprs['STAT_COL_HALFHOUR'] = 'datetime';
				break;
			default:
				return false;
		}
		$column = $this->column($columnExprs);
		$from = $this->name;
		$where = 'WHERE ' . $this->where($cond);
		$order = 'ORDER BY ABS(tms) DESC, tmr DESC';
		$limit = $this->limit($page);
		$sql = $this->sql($column, $from, $where, null, $order, $limit);

		return $this->fetchAll($sql);
	}
	
	/**
	 * @param array $cond 查询条件
	 *   token string 设备标识Token值
	 *   mid string 应用自定义ID值
	 * @param array $page limit参数
		 offset int 起始下标
		 size int 每页获取数量
	 * @return array
	 *
	 */
	public function findByField($cond, $page = array())
	{
		$column = '*';
		$from = $this->name;
		$where = 'WHERE ' . $this->where($cond);
		$order = 'ORDER BY ABS(tms) DESC, tmr DESC';
		$limit = $this->limit($page);
		$sql = $this->sql($column, $from, $where, null, $order, $limit);

		return $this->fetchAll($sql);
	}

	/*
	 * 获取hoststr数据
	 * @return array
	 */
	public function getHostList() {
		$column = 'DISTINCT hoststr';
		$from = $this->name;
		$order = 'ORDER BY hoststr';
		$sql = $this->sql($column, $from, null, null, $order);

		return $this->fetchPairs($sql, 'hoststr', 'hoststr');
	}
	
	/*
	 * 获取hoststr数据
	 * @param array $cond 条件
	 *  start int 开始时间
	 *  end int 结束时间
	 * @return array
	 */
	public function getHostListByDate($cond) {
		$column = 'DISTINCT hoststr';
		$from = $this->name;
		$where = 'WHERE ' . $this->where($cond);
		$order = 'ORDER BY hoststr';
		$sql = $this->sql($column, $from, $where, null, $order);

		return $this->fetchPairs($sql, 'hoststr', 'hoststr');
	}

	/**
	 * 根据id获取一条数据
	 * @param int $id 编号
	 * @return array
	 *
	 */
	public function findById($id)
	{
		return $this->load($id);
	}
	
}